# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
工具类，实现了对参数进行组合，生产不同组合的dict类型的参数，并将dict参数保存到list中，方便取用。 
0sheet 为测试的所有数据,从 授信申请--》借款申请--》业务办理
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import xlrd
from interfaceTest.baseset.msydloan_api import API_MSYD
import copy
from datetime import datetime
import pymysql


class parameter():
    db = pymysql.connect(
        host="10.16.20.182",  # 测试环境
        # host="10.16.20.190",     # 开发环境
        user="dev",  # 测试：dev   开发：root
        password="dev",  # 测试：dev   开发：123456
        port=3306,
        use_unicode=True,
        charset="utf8",
        database="qdzjmsloandb")

    def get_apply_data(self, xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        for i in range(1, nrows):
            row = []
            num = table.cell_value(i, 0)
            type = table.cell_value(i, 1)
            status = table.cell_value(i, 2)
            rspCd = table.cell_value(i, 14)
            if type == 'apply' and status == 'P':
                api = copy.deepcopy(API_MSYD['apply'])
                api['jrnNo'] = datetime.now().strftime('%Y%m%d%H%M%S') + num
                api['qryCreditId'] = table.cell_value(i, 3)
                api['hbUsrNo'] = table.cell_value(i, 4)
                api['usrIdName'] = table.cell_value(i, 5)
                api['mblNo'] = str(int(table.cell_value(i, 6)))
                api['usrIdCard'] = table.cell_value(i, 7)
                row.append('apply')
                row.append(api)
                row.append(rspCd)
                cellData.append(row)
        # print(cellData)
        return (cellData)

    def get_freeze_data(self, xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        for i in range(1, nrows):
            row = []
            num = table.cell_value(i, 0)
            type = table.cell_value(i, 1)
            status = table.cell_value(i, 2)
            rspCd = table.cell_value(i, 14)
            if type == 'freeze' and status == 'P':
                api = copy.deepcopy(API_MSYD['freeze'])
                api['jrnNo'] = datetime.now().strftime('%Y%m%d%H%M%S') + num
                api['hbUsrNo'] = table.cell_value(i, 4)
                api['mblNo'] = str(int(table.cell_value(i, 6)))
                api['actBrwMblNo'] = str(int(table.cell_value(i, 8)))
                api['actBrwIdNo'] = table.cell_value(i, 9)
                api['brwOrdNo'] = table.cell_value(i, 10)
                api['brwOrdDt'] = table.cell_value(i, 11)  # 和包贷借款订单日期
                api['depProvNo'] = table.cell_value(i, 12)
                api['provStgDay'] = table.cell_value(i, 13)
                row.append('freeze')
                row.append(api)
                row.append(rspCd)
                cellData.append(row)
        # print(cellData)
        return (cellData)

    def get_block_data(self, xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        for i in range(1, nrows):
            row = []
            num = table.cell_value(i, 0)
            type = table.cell_value(i, 1)
            status = table.cell_value(i, 2)
            rspCd = table.cell_value(i, 14)
            if type == 'block' and status == 'P':
                api = copy.deepcopy(API_MSYD['block'])
                api['jrnNo'] = datetime.now().strftime('%Y%m%d%H%M%S') + num
                api['hbUsrNo'] = table.cell_value(i, 4)
                api['mblNo'] = str(int(table.cell_value(i, 6)))
                api['brwOrdNo'] = table.cell_value(i, 10)
                api['brwOrdDt'] = table.cell_value(i, 11)  # 和包贷借款订单日期
                api['acpTm'] = table.cell_value(i, 15)  # 办理业务时间
                api['orgOrdNo'] = table.cell_value(i, 16)  # 资金方借款订单号
                row.append('block')
                row.append(api)
                row.append(rspCd)
                cellData.append(row)
        # print(cellData)
        return (cellData)


if __name__ == '__main__':
    data = parameter()
    # data.get_apply_data('../files/msydloan.xls')
    # data.get_freeze_data('../files/msydloan.xls')
    data.get_block_data('../files/msydloan.xls')
